package cn.ardy.common.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


import com.qunar.tts.common.exception.OPBException;

public class ExcelUtil {

	public static final int EXCEL_MAX_ROW_SIZE = 65536;
	
	
	public static void addResponseHeader(HttpServletResponse response, String fileName) {
		try {
			fileName = new String(fileName.getBytes("gb2312"), "ISO8859-1");
			response.setHeader("Content-Disposition", "attachment; filename=\""+ fileName+"\"");	
		} catch (Exception e) {
			throw new OPBException(e);
		}
		
	}
	
	public static void addResponseHeader(HttpServletResponse response, HttpServletRequest request, String fileName) {
		try {
			if(StringUtils.contains(request.getHeader("USER-AGENT"), "MSIE")){
				fileName = new String(fileName.getBytes("gb2312"), "ISO8859-1");
			}else{
				fileName = new String(fileName.getBytes(), "ISO8859-1");
			}
			if(StringUtils.contains(request.getHeader("USER-AGENT"), "Opera")){
				if(StringUtils.endsWith(fileName,".docx")){
					response.setContentType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
				}
				if(StringUtils.endsWith(fileName,".xlsx")){
					response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
				}
			}
			response.setHeader("Content-Disposition", "attachment; filename=\""+ fileName+"\"");	
		} catch (Exception e) {
			throw new OPBException(e);
		}
	}
	
	/**
	 * 分页输出excel
	 * @param os 输出流
	 * @param title sheet的名字
	 * @param columnInfos 列信息
	 * @param data 数据信息
	 * @param numPerPage 每页行数
	 */
	
	
	public static WritableCellFormat getDefaultTitleFormat() {
		WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
		WritableCellFormat titleFormat = new WritableCellFormat(font);
		{
			try {
				titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
				titleFormat.setAlignment(Alignment.CENTRE);
			} catch (WriteException e) {
				throw new OPBException(e);
			}
			
		}
		return titleFormat;
	}

	public static WritableCellFormat getDefaultTextFormat() {
		WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
		WritableCellFormat cellFormat = new WritableCellFormat(font);
		{
			try {
				cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
				cellFormat.setAlignment(Alignment.RIGHT);
			} catch (WriteException e) {
				throw new OPBException(e);
			}
		}
		return cellFormat;
	}
	
	public static WritableCellFormat getDefaultNumberFormat() {
		return new WritableCellFormat();
	}
	
	public static void toExcel(OutputStream os,String title,List<ColumnInfo> columnInfos,List<String[]> data, int numPerPage) {
		try {
			WorkbookSettings ws = new WorkbookSettings();
			WritableWorkbook workbook =Workbook.createWorkbook(os,ws);
			
			int pages = data.size() / numPerPage + 1;
			
			for (int page = 0; page < pages; page++) {
				String subTitle = title;
				if(page>0){
					subTitle = title + "-" + page;
				}
				WritableSheet listSheet = workbook.createSheet(subTitle, page);
				List<String[]> subData = data.subList(numPerPage*page,Math.min(data.size(),numPerPage*(page+1)));
				if(subData == null || subData.size() == 0){
					continue;
				}
				for (int i = 0; i < columnInfos.size(); i++) {
					ColumnInfo columnInfo = columnInfos.get(i);
					listSheet.addCell(new Label(i,0,columnInfo.name,columnInfo.titleFormat));			
				}
				
				for (int index = 0;index < subData.size();index++) {
					String[] row = subData.get(index);
					int rowIndex = index+1;
					for (int i = 0; i < columnInfos.size(); i++) {		
						ColumnInfo columnInfo = columnInfos.get(i);
						if(isDouble(row[i])){
							listSheet.addCell(new jxl.write.Number(i,rowIndex,NumberUtils.toDouble(row[i]),columnInfo.cellFormat));
						}else{
							listSheet.addCell(new Label(i,rowIndex,row[i],columnInfo.cellFormat));
						}
						
					}
				}
				
				for (int i = 0; i < columnInfos.size(); i++) {
					ColumnInfo item = columnInfos.get(i);
					listSheet.setColumnView(i, item.width);//A
				}
			}
			
			
			workbook.write();
			workbook.close();
		} catch (Exception e) {
			throw new OPBException(e);
		}
	}
	public static boolean isDouble(String str) {  
		if(str == null || "".equals(str)){
			 return false;
		}
		Pattern pattern = Pattern.compile("^[-\\+]?[.\\d]*$");
		return pattern.matcher(str).matches();
	}  
	public static class ColumnInfo{		
		private String name;
		private int width;
		private WritableCellFormat titleFormat;
		private WritableCellFormat cellFormat;
		
		public ColumnInfo() {
		}
		

		public ColumnInfo(String name, int width, 
				WritableCellFormat titleFormat, WritableCellFormat cellFormat) {
			this.name = name;
			this.width = width;
			this.titleFormat = titleFormat;
			this.cellFormat = cellFormat;
		}
		
		public String getName() {
			return name;
		}

		public void setName(String name) {
			this.name = name;
		}

		public int getWidth() {
			return width;
		}

		public void setWidth(int width) {
			this.width = width;
		}

		public WritableCellFormat getTitleFormat() {
			return titleFormat;
		}

		public void setTitleFormat(WritableCellFormat titleFormat) {
			this.titleFormat = titleFormat;
		}

		public WritableCellFormat getCellFormat() {
			return cellFormat;
		}

		public void setCellFormat(WritableCellFormat cellFormat) {
			this.cellFormat = cellFormat;
		}
	}
	
	public static void main(String[] args) throws Exception {
		OutputStream os = new FileOutputStream(new File("test.xls"));
		List<ColumnInfo> columnInfos = Arrays.asList(new ColumnInfo("年龄", 100, getDefaultTextFormat(), getDefaultNumberFormat()), new ColumnInfo("姓名", 50,getDefaultTextFormat(), getDefaultNumberFormat()));
		List<String[]> data = Arrays.asList(new String[]{
			"123", "abc"
		}, new String[]{
			"321", "xyq"
		});
		toExcel(os, "test",columnInfos, data, 500);
		os.close();
	}
}